package com.gzy.studentsociety.dao;

import com.gzy.studentsociety.entity.Member;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * @version: java version 1.8
 * @Author: Chuan Liu
 * @description:
 * @date: 2024-11-13 23:05
 */
public class MemberDaoImpl extends BaseDao implements MemberDao {
    // 添加member
    @Override
    public Integer addMember(Member member) throws SQLException {
        String sql = "insert into member (memberStudentId,joinDate,member_societyId) value (?,?,?)";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.update(sql,member.getMemberStudentId(),member.getJoinDate(),member.getMemberAssociationId());
    }

    // 判断是否是 member
    @Override
    public Integer isMemberIn(Member member) throws SQLException {
        String sql = "select count(1) from member where member_societyId = ? and memberStudentId = ?";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.query(sql, new ScalarHandler<Long>(),member.getMemberAssociationId(), member.getMemberStudentId()).intValue();
    }

    // 查询所有
    @Override
    public List<Member> queryAllMember(Integer status, Integer societyId) throws SQLException {
        String sql = "select * from member where memberStatus = ? and member_societyId = ?";
        Map<String, String> columnToPropertyOverrides = new HashMap<>();
        columnToPropertyOverrides.put("member_societyId", "memberAssociationId");

        BeanProcessor beanProcessor = new BeanProcessor(columnToPropertyOverrides);
        RowProcessor rowProcessor = new BasicRowProcessor(beanProcessor);

        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.query(sql, new BeanListHandler<>(Member.class, rowProcessor), status, societyId);
    }


    // 删除全部
    public Integer deleteGraduated(Integer assId) throws SQLException {
        String sql = "update member,student set member.memberStatus = 2 where member.member_societyId = ?" +
                " and member.memberStudentId = student.studentId and student.graduated = 2";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.update(sql,assId);
    }

    // 删除成员
    @Override
    public Integer deleteMember(Integer id, Integer status) throws SQLException {
        String sql = "update member set member.memberStatus = ? where memberId = ?";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.update(sql,status,id);
    }

    @Override
    //假设学生只参加一个协会
    public Member queryByNumber(String number) throws SQLException {
        String sql = "select m.* from member m,student s where s.studentNumber = ? and m.memberStudentId = s.studentId";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.query(sql,new BeanHandler<Member>(Member.class),number);
    }




    //    main
    public static void main(String[] args) {
        try {
            MemberDao dao = new MemberDaoImpl();
            System.out.println(dao.queryByNumber("2022034567"));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}
